package com.thiny.android.app.tnote.datebase;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.thiny.android.app.tnote.Constants;
import com.thiny.android.app.tnote.dao.SQLTable;
import com.thiny.android.app.tnote.model.Note;
import com.thiny.android.app.tnote.util.TimeUtil;

public class SQLiteHelper extends SQLiteOpenHelper {

	private static final String DB_NAME = "note_db";
	private static final int DB_VERSION = 1;
	private static final String NOTE_TABLE = "note_table";

	private static SQLiteHelper instance;

	public static SQLiteHelper getInstance(Context context) {
		if (instance == null) {
			instance = new SQLiteHelper(context);
		}
		return instance;
	}

	private SQLiteHelper(Context context) {
		this(context, DB_NAME, null, DB_VERSION);
	}

	private SQLiteHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		createTable(db, getColumns());
	}

	private void createTable(SQLiteDatabase db, Map<String, String> columns) {
		Set<String> keys = columns.keySet();
		StringBuffer sql = new StringBuffer();
		sql.append("CREATE TABLE " + NOTE_TABLE + " ( ");
		for (String key : keys) {
			sql.append(key + " " + columns.get(key) + ",");
		}
		int length = sql.length();
		db.execSQL(sql.substring(0, length - 1) + ")");
	}

	private Map<String, String> getColumns() {
		Map<String, String> columns = new HashMap<String, String>();
		// 某天的零点
		columns.put(Constants.NOTE_DATE, SQLTable.TYPE_LONG);
		// columns.put(Constants.NOTE_ID, SQLTable.TYPE_LONG);
		columns.put(Constants.NOTE_CREATE_TIME, SQLTable.TYPE_LONG);
		columns.put(Constants.NOTE_CHANGE_TIME, SQLTable.TYPE_LONG);
		columns.put(Constants.NOTE_ALARM_TIME, SQLTable.TYPE_LONG);
		columns.put(Constants.NOTE_TITLE, SQLTable.TYPE_TEXT);
		columns.put(Constants.NOTE_CONTENT, SQLTable.TYPE_TEXT);
		// columns.put(Constants.NOTE_ID, SQLTable.TYPE_LONG);
		// columns.put("id", SQLTable.TYPE_LONG);
		return columns;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public boolean insert(Note note) {
		return getDatabase().insert(NOTE_TABLE, null, getContentValues(note)) != -1;
	}

	private ContentValues getContentValues(Note note) {
		ContentValues values = new ContentValues();
		values.put(Constants.NOTE_DATE, note.getDate());
		values.put(Constants.NOTE_CREATE_TIME, note.getCreateTime());
		values.put(Constants.NOTE_CHANGE_TIME, note.getChangeTime());
		values.put(Constants.NOTE_ALARM_TIME, note.getAlarmTime());
		values.put(Constants.NOTE_TITLE, note.getTitle());
		values.put(Constants.NOTE_CONTENT, note.getContent());
		return values;
	}

	public void delete(long date) {
		getDatabase().delete(NOTE_TABLE, Constants.NOTE_DATE + "=?",
				new String[] { "" + date });
	}

	private Cursor queryByCursor(String[] columns, String selection,
			String[] selectionArgs, String orderBy) {
		return getDatabase().query(NOTE_TABLE, columns, selection,
				selectionArgs, null, null, orderBy);
	}

	public Note findByDate(int date) {
		Cursor cursor = queryByCursor(new String[] { "*" }, "date =?",
				new String[] { date + "" }, null);
		return findByCursor(cursor);
	}

	public Note findByCursor(Cursor cursor) {
		Note note = new Note();
		int index = cursor.getColumnIndex(Constants.NOTE_DATE);
		note.setDate(cursor.getLong(index));
		index = cursor.getColumnIndex(Constants.NOTE_CREATE_TIME);
		note.setCreateTime(cursor.getLong(index));
		index = cursor.getColumnIndex(Constants.NOTE_CHANGE_TIME);
		note.setChangeTime(cursor.getLong(index));
		index = cursor.getColumnIndex(Constants.NOTE_ALARM_TIME);
		note.setAlarmTime(cursor.getLong(index));
		index = cursor.getColumnIndex(Constants.NOTE_TITLE);
		note.setTitle(cursor.getString(index));
		index = cursor.getColumnIndex(Constants.NOTE_CONTENT);
		note.setContent(cursor.getString(index));
		return note;
	}

	public List<Note> findAll() {
		Cursor cursor = queryByCursor(new String[] { "*" }, null, null, null);
		return findListByCursor(cursor);
	}

	public List<Note> findAllInMonth(long monthTime) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTimeInMillis(monthTime);
		calendar.set(Calendar.MILLISECOND, 0);
		calendar.set(Calendar.DAY_OF_MONTH, 1);
		calendar.set(Calendar.HOUR_OF_DAY, 0);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		long min = calendar.getTimeInMillis();
		long max = min + TimeUtil.DAY_MILLISECOND
				* calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
		StringBuilder selection = new StringBuilder();
		selection.append(Constants.NOTE_DATE).append(">=? AND ")
				.append(Constants.NOTE_DATE).append("<?");
		StringBuilder orderBy = new StringBuilder();
		orderBy.append(Constants.NOTE_DATE).append(" ASC");
		Cursor cursor = queryByCursor(new String[] { "*" },
				selection.toString(), new String[] { min + "", max + "" },
				orderBy.toString());
		return findListByCursor(cursor);
	}

	public List<Note> findListByCursor(Cursor cursor) {
		List<Note> list = new ArrayList<Note>();
		cursor.moveToFirst();
		try {
			while (!cursor.isAfterLast()) {
				Note note = findByCursor(cursor);
				list.add(note);
				cursor.moveToNext();
			}
		} finally {
			cursor.close();
		}

		return list;
	}

	public SQLiteDatabase getDatabase() {
		return getWritableDatabase();
	}
}
